# coding=utf-8
import sys

import MySQLdb
import xlrd

reload(sys)
sys.setdefaultencoding('utf-8')

Host = "118.31.32.197"
Port = 3306
User = "gmj"
Passwd = "123456"
Db = "worldcup"

conn = MySQLdb.connect(host=Host, port=Port, user=User, passwd=Passwd, db=Db, charset="utf8")
cursor = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

file_path = "订单信息.xlsx"
excel = xlrd.open_workbook(file_path)
table = excel.sheets()[0]

for i in range(table.nrows):
	row = table.row_values(i)
	name = str(row[0])
	tel = str(int(row[1]))
	kuaididan = row[4]
	if kuaididan == "":
		continue
	kuaididan = str(int(kuaididan))
	sql = "select id from userModel_orderhead where order_status = 25 and address like '%" + name + "%' and address like '%" + tel + "%'"
	cursor.execute(sql)
	ids = cursor.fetchall()
	for id in ids:
		# print id
		sql = "update userModel_orderhead set express_company = '申通', express_code = '" + kuaididan + "' where id = " + str(id["id"])
		print sql
		cursor.execute(sql)
cursor.execute("commit")



